package cn.edu.sdjzu.xg.bysj.dao;
//201902104061徐中堂
import cn.edu.sdjzu.xg.bysj.domain.Student;
import cn.edu.sdjzu.xg.bysj.domain.StudentClass;
import cn.edu.sdjzu.xg.bysj.domain.Teacher;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;
import util.CommonDao;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.*;
import java.util.Collection;
import java.util.TreeSet;

@Slf4j public class StudentDao {
    private static StudentDao studentDao = new StudentDao();
    private StudentDao(){}
    public static StudentDao getInstance(){
        return studentDao;
    }

    /**
     * 根据id在数据库中查询对象信息
     * @param connection
     * @param id
     * @return
     * @throws SQLException
     */
    public Student find(Connection connection,int id) throws SQLException {
        Student student = null;
        //预编译
        PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM student WHERE id=?");
        //赋id值
        preparedStatement.setInt(1,id);
        //执行并获得查询到的学生结果集
        ResultSet resultSet = preparedStatement.executeQuery();
        //读取信息为对象赋值
        if (resultSet.next()){
            int student_id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            String no = resultSet.getString("no");
            String remarks = resultSet.getString("remarks");
            Integer teacher_id = resultSet.getInt("teacher_id");
            Integer studentClass_id = resultSet.getInt("studentClass_id");
            Teacher teacher = TeacherDao.getInstance().find(connection,teacher_id);
            StudentClass studentClass = StudentClassDao.getInstance().find(connection,studentClass_id);
            student = new Student(student_id,name,no,remarks,studentClass);
            student.setSupervisor(teacher);
        }
        //关闭
        JdbcHelper.close(resultSet,preparedStatement);
        //返回查询到的对象信息
        return student;
    }

    /**
     * 条件+分页查询
     * @param connection
     * @param conditions
     * @param pagination
     * @return
     * @throws SQLException
     */
    public Collection<Student> findAll(Connection connection , Collection<Condition> conditions , Pagination pagination) throws SQLException {
        //schools集合用于储存查找到的数据
        Collection<Student> students = new TreeSet<Student>();
        //获得元组总数
        int totalNum = CommonDao.getInstance().count(connection,"School");
        //构建查找sql语句
        String sql_select_all = "SELECT * FROM student ";
        if (conditions != null){
            sql_select_all += Condition.toWhereClause(JSON.toJSONString(conditions));
            log.trace(sql_select_all);
        }
        if (pagination != null){
            //如果带有分页请求，则根据请求创建对应的LIMIT子句
            sql_select_all += pagination.toLimitClause(totalNum);
            log.trace(sql_select_all);
        }
        //预编sql语句
        PreparedStatement preparedStatement = connection.prepareStatement(sql_select_all);
        //执行，获得结果集
        ResultSet resultSet = preparedStatement.executeQuery();
        //查找到的对象添加进集合中
        while (resultSet.next()){
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            String no = resultSet.getString("no");
            String remarks = resultSet.getString("remarks");
            Integer teacher_id = resultSet.getInt("teacher_id");
            Integer studentClass_id = resultSet.getInt("studentClass_id");
            Teacher teacher = TeacherDao.getInstance().find(connection,teacher_id);
            StudentClass studentClass = StudentClassDao.getInstance().find(connection,studentClass_id);
            Student student = new Student(id,name,no,remarks,studentClass);
            student.setSupervisor(teacher);
            students.add(student);
        }
        //关闭
        JdbcHelper.close(resultSet,preparedStatement);
        //返回查找到的结果集
        return students;
    }

    /**
     * 查询老师下属学生
     * @param connection
     * @param teacher
     * @param pagination
     * @return
     * @throws SQLException
     */
    public Collection<Student> findAll(Connection connection , Teacher teacher ,Pagination pagination ) throws SQLException {
        Collection<Student> students = new TreeSet<>();
        //构建查询sql语句
        StringBuilder select_teacher_student_sql = new StringBuilder("SELECT * FROM Student WHERE teacher_id=? ");
        //构建分页子句
        if (pagination != null){
            int totalNum = CommonDao.getInstance().count(connection,"Student");
            select_teacher_student_sql.append(pagination.toLimitClause(totalNum));
            log.trace(select_teacher_student_sql.toString());
        }
        //预编译
        PreparedStatement preparedStatement = connection.prepareStatement(select_teacher_student_sql.toString());
        //设置查找的id
        preparedStatement.setInt(1,teacher.getId());
        //执行并获得查找到的学生结果集
        ResultSet resultSet = preparedStatement.executeQuery();
        //读取信息为对象赋值并添加到集合中
        while (resultSet.next()){
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            String no = resultSet.getString("no");
            String remarks = resultSet.getString("remarks");
            Integer studentClass_id = resultSet.getInt("studentClass_id");
            StudentClass studentClass = StudentClassDao.getInstance().find(connection,studentClass_id);
            Student student = new Student(id,name,no,remarks,studentClass);
            student.setSupervisor(teacher);
            students.add(student);
        }
        //关闭
        JdbcHelper.close(resultSet,preparedStatement);
        //返回查找到的结果集
        return students;
    }

    /**
     * 添加
     * @param connection
     * @param student
     * @return
     * @throws SQLException
     */
    public boolean add(Connection connection , Student student) throws SQLException {
        //添加并获得id值
        PreparedStatement preparedStatement =
                connection.prepareStatement(
                        "INSERT INTO " +
                        "Student (id,name,no,remarks,teacher_id,studentClass_id)" +
                        "VALUES(?,?,?,?,?,?)"
                );
        //设置添加信息参数
        preparedStatement.setInt(1,student.getId());
        preparedStatement.setString(2,student.getName());
        preparedStatement.setString(3,student.getNo());
        preparedStatement.setString(4,student.getRemarks());
        preparedStatement.setInt(5,student.getSupervisor().getId());
        preparedStatement.setInt(6,student.getStudentClass().getId());
        //执行并获得受影响行数
        int rowAffected = preparedStatement.executeUpdate();
        //关闭
        JdbcHelper.close(null,preparedStatement);
        //返回添加是否成功
        return rowAffected>0;
    }

    /**
     * 更新
     * @param connection
     * @param student
     * @return
     * @throws SQLException
     */
    public boolean update(Connection connection , Student student) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(
                "UPDATE Student SET name=?,no=?,remarks=?,teacher_id=?,studentClass_id=? WHERE id=?");
        preparedStatement.setString(1,"name");
        preparedStatement.setString(2,"no");
        preparedStatement.setString(3,"remarks");
        preparedStatement.setInt(4,student.getSupervisor().getId());
        preparedStatement.setInt(5,student.getStudentClass().getId());
        preparedStatement.setInt(6,student.getId());
        int rowAffected = preparedStatement.executeUpdate();
        JdbcHelper.close(null,preparedStatement);
        return rowAffected>0;
    }

    /**
     * 根据id删除
     * @param connection
     * @param id
     * @return
     * @throws SQLException
     */
    public boolean deleted(Connection connection , int id) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement("DELETE FROM student WHERE id=?");
        preparedStatement.setInt(1,id);
        int rowAffected = preparedStatement.executeUpdate();
        JdbcHelper.close(null,preparedStatement);
        return rowAffected > 0;
    }
}
